--Dropping all objects in the schema
--Metadata version
create table meta_version(
    --Metadata component
    major numeric(5) not null,
    --Component version
    minor numeric(5) not null
    );
    
go
    
insert into meta_version(major, minor)
values (3, 0); --Do not forget to update the installer version in code

go

--Table of dbms features
create table meta_dbms_features(
    --Feature name
    feature_name varchar(255) primary key,
    --Accessability
    is_enabled numeric(1) not null,
    
    check(is_enabled = 0 or is_enabled = 1),
    
    check(len(rtrim(ltrim(feature_name))) > 0)
);

go

--Adding features
insert into meta_dbms_features values('COLUMN_RENAMING', 1);

go

--Options table
create table meta_options(
    --Option name
    option_name varchar(255) primary key,
    --Integer value
    int_value numeric(10));
    
go

insert into meta_options(option_name, int_value) values('RECYCLE_BIN', 1);

go

--Tables metadata
create table meta_tables(
    --Table id (guid)
    table_id binary(16) not null primary key,
    --Table name (30 - length of 'tbl_')
    table_name varchar(26) not null unique,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Unit level security
    unit_level_security numeric(1) default 1 not null,    
    
    check (len(ltrim(rtrim(table_name))) > 0));

go

--Table with string fields metadata
create table meta_string_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Description
    description nvarchar(1000),
    --Default value
    default_value nvarchar(2000),
    --Max length
    max_length numeric(4),
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
    
    unique(table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(ltrim(rtrim(column_name))) > 0),
           
    check (max_length > 0),
           
    check(is_not_null = 0 or is_not_null = 1),
    
    check(customization_level >= 0)
);

go

--Table of reference columns
create table meta_ref_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value binary(16),
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
    --The need to check reference values in the column
    ref_checking numeric(1) default 1 not null,
    
    unique(table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0),
    
    check(ref_checking = 0 or ref_checking = 1)
    );
  
go
    
--Table of referenced tables
create table meta_ref_targets(
       --Column id
       column_id binary(16),
       --Referenced table id
       ref_table_id binary(16),
       --Cascade delete
       cascade_delete numeric(1) default 0 not null,
       
       primary key (column_id, ref_table_id),
       
       foreign key (ref_table_id) 
       references meta_tables(table_id)
       --No cascade delete !!!
       ,
       
       foreign key (column_id) 
       references meta_ref_columns(column_id)
       on delete cascade
       );

go    

--Table of enum columns
create table meta_enum_columns (
    --Column id
    column_id binary(16) primary key,        
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value numeric(10),
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
    --Value checking
    value_checking numeric(1) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
    
    check(customization_level >= 0),
    
    check(len(rtrim(ltrim(column_name))) > 0)
);

go

--Table of enum members
create table meta_enum_members (
    --Column id
    column_id binary(16),    
    --Value
    value numeric(10) not null,
    --Display name
    display_name nvarchar(255),
           
    primary key(column_id, value),
           
    foreign key(column_id)
    references meta_enum_columns(column_id)
    on delete cascade    
);

go

alter table meta_enum_columns
add foreign key (column_id, default_value) 
references meta_enum_members(column_id, value);

go

--Table of numeric columns
create table meta_decimal_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name varchar(255),
    --Description
    description varchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value numeric(38,5),
    --Minimum
    minimum numeric(38,5),
    --Maximum
    maximum numeric(38,5),
    --Precision
    precision numeric(2),
    --Scale
    scale numeric(2),
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
           
    check (precision > 0),
           
    check (scale >= 0),
           
    check (precision >= scale),
    
    check(customization_level >= 0)
);

go

--Table of date columns
create table meta_datetime_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value datetime,
    --Order index
    order_index numeric(10) default 0 not null,
    --Minimum
    minimum datetime,
    --Maximum
    maximum datetime,
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of boolean columns
create table meta_bool_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value numeric(1),
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of integer columns
create table meta_int_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value numeric(10),
    --Order index
    order_index numeric(10) default 0 not null,
    --Minimum
    minimum numeric(10),
    --Maximum
    maximum numeric(10),
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of binary columns
create table meta_binary_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of big integer columns
create table meta_big_int_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value numeric(20),
    --Order index
    order_index numeric(10) default 0 not null,
    --Minimum
    minimum numeric(20),
    --Maximum
    maximum numeric(20),
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of small integer columns
create table meta_small_int_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value numeric(5),
    --Order index
    order_index numeric(10) default 0 not null,
    --Minimum
    minimum numeric(5),
    --Maximum
    maximum numeric(5),
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of guid columns
create table meta_guid_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Default value
    default_value binary(16),
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of text columns
create table meta_text_columns (
    --Column id
    column_id binary(16) primary key,    
    --Table id
    table_id binary(16) not null,
    --Column name
    column_name varchar(26) not null,
    --Display name
    display_name nvarchar(255),
    --Description
    description nvarchar(1000),
    --Nullability
    is_not_null numeric(1) default 0 not null,
    --Order index
    order_index numeric(10) default 0 not null,
    --Customization level
    customization_level numeric(10) default 1 not null,
           
    unique (table_id, column_name),
           
    foreign key (table_id) 
    references meta_tables(table_id)
    on delete cascade,
           
    check (len(rtrim(ltrim(column_name))) > 0),
    
    check(customization_level >= 0)
);

go

--Table of indexes
create table meta_indexes (
    --Index id
    index_id binary(16) primary key,
    --Table id
    table_id binary(16) references meta_tables(table_id) on delete cascade not null,
    --Is unique
    is_unique numeric(1) default 0 not null,
    --User id list
    column_id_list varbinary(256) not null,
    
    check(is_unique = 0 or is_unique = 1)
    );
    
go

create index ie1d05b62fe8b4367a938575f795f on meta_indexes(table_id);

go

go

create index i7a92ea5e389945ad8ec9d244b7d1 on meta_indexes(column_id_list);

go

--Table of business units
create table meta_units (
    --Unit id
    unit_id binary(16) primary key,
    --Unit name
    unit_name varchar(26) not null unique,
    --Display name
    display_name nvarchar(255)
);

go

--Trigger that handles deleting unit child and parent entries
create trigger meta_units_delete
on meta_units instead of delete
as
begin
    set nocount on;
    
    delete meta_unit_parents
    where unit_id in (select unit_id from deleted);
    
    delete meta_unit_parents
    where parent_unit_id in (select unit_id from deleted);
    
    set nocount off;
    
    delete meta_units
    where unit_id in (select unit_id from deleted);
end;

go

--Table of unit parents
create table meta_unit_parents (
    --Unit id
    unit_id binary(16) not null
    references meta_units(unit_id),
    --Parent unit id
    parent_unit_id binary(16) not null
    references meta_units(unit_id),
    
    primary key (unit_id, parent_unit_id)
);

go

create index icb90cb191a5749d3b4288d474ef4 on meta_unit_parents(unit_id);

go

create index iCEC76AAAC7CD448FA7A528D38A19 on meta_unit_parents(parent_unit_id);

go

--Table of users
create table meta_users (
    --User name
    user_name varchar(26) primary key,
    --Display name
    display_name nvarchar(255),
    --Can login
    can_login numeric(1) default 0 not null,
    --Can change security
    can_change_security numeric(1) default 0 not null,
    --Can change the schema
    can_change_schema numeric(1) default 0 not null,
    
    check(can_login = 0 or can_login = 1),
    
    check(can_change_security = 0 or can_change_security = 1),
    
    check(can_change_schema = 0 or can_change_schema = 1),
    
    check(len(rtrim(ltrim(user_name))) > 0)
);

go

--Table of user units
create table meta_user_units(
    --User name
    user_name varchar(26)
    references meta_users(user_name)
    on delete cascade,
    --Unit id
    unit_id binary(16)
    references meta_units(unit_id)
    on delete cascade,
    
    primary key(user_name, unit_id)
    );

go

create index i4aa1ea93585e4e6185e7763b9c1d on meta_user_units(user_name);

go

create index i9C4A67BDE28B465CBE62C0E8B274 on meta_user_units (unit_id);

go

--Table of roles
create table meta_roles (
    --Role name
    role_name varchar(26) primary key,
    --Display name
    display_name nvarchar(255),
    
    check(len(rtrim(ltrim(role_name))) > 0)
);

go

--Table of user roles
create table meta_user_roles (
    --User name
    user_name varchar(26) not null
    references meta_users(user_name) on delete cascade,
    
    --Container role name
    container_role_name varchar(26) not null
    references meta_roles(role_name) on delete cascade,
    
    primary key (user_name, container_role_name)
);

go

create index iF60C2C629EFA4B81A07C41DA89E0 on meta_user_roles(user_name);

go

create index i679DC1B7E3DC43A5A1EFE333A640 on meta_user_roles(container_role_name);

go

--Trigger that handles deleting child and parent role entries
create trigger meta_roles_delete
on meta_roles instead of delete
as
begin
    set nocount on;
    
    delete from meta_role_roles
    where role_name in (select role_name from deleted);
    
    delete from meta_role_roles
    where container_role_name in (select role_name from deleted);
    
    set nocount off;
    
    delete from meta_roles
    where role_name in (select role_name from deleted);
end;

go

--Table of role roles
create table meta_role_roles (
    --Role name
    role_name varchar(26) not null
    references meta_roles(role_name),
    
    --Container role name
    container_role_name varchar(26) not null
    references meta_roles(role_name),
    
    primary key (role_name, container_role_name),
    
    check (role_name <> container_role_name)
);

go

create index i13D09851537D45C090272DD9E5F6 on meta_role_roles(role_name);

go

create index i96DE55D54C5E41E8A51E9C550259 on meta_role_roles(container_role_name);

go

--Table of table ACLs
create table meta_table_acl(
    --Table id
    table_id binary(16) not null
    references meta_tables(table_id) on delete cascade,
    --Principal name
    principal_name varchar(26),
    --Access type
    access_type numeric(1) default 1 not null,
    --Access level
    access_level numeric(1) default 1 not null,
    
    primary key(table_id, principal_name, access_type),
    
    check(access_type = 1 or access_type = 2
    or access_type = 3 or access_type = 4),
    
    check(access_level = 1 or access_level = 2
    or access_level = 3 or access_level = 4 or access_level = 5 or access_level = 6)
);

go 

create index ia805f9fe90ba4b078b9e5da43474 on meta_table_acl(principal_name);

go 

create index i2eb3d75505344611b2329c8b3fb7 on meta_table_acl(table_id);

go 

create index i2f0dd14b392c4ef1b9688589bacc on meta_table_acl(table_id, principal_name);

go

--Table acl unit-level access rights
create table meta_table_acl_unit_access(
    --Table id
    table_id binary(16) not null references meta_tables(table_id)
    on delete cascade,
    --User name
    user_name varchar(26) not null references meta_users(user_name)
    on delete cascade,
    --Unit id
    unit_id binary(16) not null references meta_units(unit_id)
    on delete cascade,
    --Table access right
    access_type numeric(1) not null,
    
    primary key (table_id, user_name, unit_id, access_type),
    
    check(access_type = 1 or access_type = 2 or access_type = 3
    or access_type = 4)
    );
    
go 

create index i52da6722730844559f532a1826b3 on meta_table_acl_unit_access(table_id, user_name, access_type);

go

create index i99B82725D3AC42C28F316E2C7B69 on meta_table_acl_unit_access(table_id, user_name);

go

create index i7A3A7D03D9EA41C1B082C7395710 on meta_table_acl_unit_access(user_name);

go

create index iB78AF4E5F2094F3083507DE65658 on meta_table_acl_unit_access(table_id);

go

--Table of full access rights
create table meta_table_acl_full_access(
     --Table id
    table_id binary(16) not null references meta_tables(table_id)
    on delete cascade,
    --User name
    user_name varchar(26) not null references meta_users(user_name)
    on delete cascade,
    --Table access type
    access_type numeric(1) not null,
    
    primary key (table_id, user_name, access_type),
    
    check(access_type = 1 or access_type = 2 or access_type = 3
    or access_type = 4)
    );
    
go

create index if5b4c8e2be454cfbbf620dc0b0d6 on meta_table_acl_full_access(table_id);

go

create index i3bfa7e7c2e6c4ed080741ed48921 on meta_table_acl_full_access(user_name);

go

--Table acl of own rows access
create table meta_table_acl_own_rows(
     --Table id
    table_id binary(16) not null references meta_tables(table_id)
    on delete cascade,
    --User name
    user_name varchar(26) not null references meta_users(user_name)
    on delete cascade,
    --Table access type
    access_type numeric(1) not null,
    
    primary key (table_id, user_name, access_type),
    
    check(access_type = 1 or access_type = 2 or access_type = 3
    or access_type = 4)
    );

go

create index i910e81f5f0d64ff8be15d6f313be on meta_table_acl_own_rows(table_id);

go

create index i6410d0e4cc5e45b3b9ea4e33de16 on meta_table_acl_own_rows(user_name);

go